package com.hainan.bike.common.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;
import org.apache.log4j.Logger;

import com.sun.rowset.CachedRowSetImpl;


public class DBObject {
	private static Logger log = Logger.getLogger(DBObject.class);

	protected Connection conn = null;
	protected Statement stmt = null;
	protected PreparedStatement pstmt = null;

	public DBObject(String dbLink) throws SQLException {
		conn = getInitConnection(dbLink);
		stmt = conn.createStatement();
	}

	private Connection getInitConnection(String dbLink) {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbLink);
		} catch (SQLException sqle) {
			log.error("get mysql db connection error : " + sqle);
		}
		return conn;
	}

	public Connection getConnection() {
		return conn;
	}

	public PreparedStatement getPreparedStatement(String sql) throws SQLException {
		pstmt = conn.prepareStatement(sql);
		return pstmt;
	}

	public Statement getStatement() throws SQLException {
		return conn.createStatement();
	}

	public void beginTransition() throws SQLException {
		try {
			conn.setAutoCommit(false);
		} catch (SQLException sqle) {
			log.error("begin transition error: " + sqle);
			throw sqle;
		}
	}

	public void commit() throws SQLException {
		try {
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException sqle) {
			log.error("commit error: " + sqle);
			throw sqle;
		}
	}

	public void rollback() throws SQLException {
		try {
			conn.rollback();
			conn.setAutoCommit(true);
		} catch (SQLException sqle) {
			log.error("rollback error: " + sqle);
			throw sqle;
		}
	}

	public void close() throws SQLException {
		try {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException sqle) {
			log.error("close error: " + sqle);
			throw sqle;
		}
	}

	public ResultSet executeQuery(String sql) throws SQLException {
		try {
			return stmt.executeQuery(sql);
		} catch (SQLException sqle) {
			log.error("executeQuery error: " + sqle);
			throw sqle;
		}
	}

	public RowSet executeRowSetQuery(String sql) throws SQLException {
		try {
			return DbAccess.executeRowSetQuery(conn, sql, 0);
		} finally {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
	}

	public int executeUpdate(String sql) throws SQLException {
		try {
			return stmt.executeUpdate(sql);
		} catch (SQLException sqle) {
			log.error("executeUpdate error: " + sqle);
			throw sqle;
		}
	}

	public int executeInsert(String sql) throws SQLException {
		int result = 0;
		try {
			//stmt.executeQuery(sql);
			stmt.execute(sql);
			String sqlId = "select last_insert_id()";
			ResultSet rs = stmt.executeQuery(sqlId);
			if (rs.next()) {
				result = rs.getInt(1);
			}
			rs.close();
		} catch (SQLException sqle) {
			log.error("executeInsert error: " + sqle);
			throw sqle;
		}
		return result;
	}

	public ResultSet executePstmtQuery() throws SQLException {
		try {
			return pstmt.executeQuery();
		} catch (SQLException sqle) {
			log.error("executePstmtQuery error: " + sqle);
			throw sqle;
		}
	}
	
	public RowSet executePstmtRowSetQuery() throws SQLException {
		CachedRowSet crset = new CachedRowSetImpl();
		try {
			ResultSet rs = pstmt.executeQuery();
			
			crset.populate(rs);
			return crset;
		} catch (SQLException sqle) {
			log.error("executePstmtQuery error: " + sqle);
			throw sqle;
		}
	}

	public int executePstmtUpdate() throws SQLException {
		try {
			return pstmt.executeUpdate();
		} catch (SQLException sqle) {
			log.error("executePstmtUpdate error: " + sqle);
			throw sqle;
		}
	}

	public int executePstmtInsert() throws SQLException {
		int result = 0;
		try {
			pstmt.execute();

			String sqlId = "select last_insert_id()";
			ResultSet rs = pstmt.executeQuery(sqlId);
			if (rs.next()) {
				result = rs.getInt(1);
			}
			rs.close();
		} catch (SQLException sqle) {
			log.error("executePstmtInsert error: " + sqle);
			throw sqle;
		}
		return result;
	}

	public int executePstmtInsert(String sql) throws SQLException {
		int result = 0;
		try {
			pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();
			if (rs.next()) {
				result = rs.getInt(1);
			}
			rs.close();
		} catch (SQLException sqle) {
			log.error("executePstmtInsert error: " + sqle);
			throw sqle;
		}
		return result;
	}
}

